/***********************************************************************************************************************************
 *    Name: Watchdog.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Watch for "REPL-Distribution" jobs which are idle.
**/
USE msdb;
DECLARE @tsql_command NVARCHAR(MAX)= N'SET NOCOUNT ON;
WITH ja (job_id, stop_execution_date, ordinal) AS (SELECT job_id, stop_execution_date, RANK() OVER (PARTITION BY job_id ORDER BY start_execution_date DESC) FROM msdb.dbo.sysjobactivity)
SELECT j.name as [Job Name]
  FROM ja INNER JOIN msdb.dbo.sysjobs j ON (ja.job_id = j.job_id)
	INNER JOIN msdb.dbo.syscategories c ON (j.category_id = c.category_id)
  WHERE ja.ordinal = 1 AND c.name = N''REPL-Distribution'' AND ja.stop_execution_date IS NOT NULL;';
EXECUTE (@tsql_command);
IF 0 < @@ROWCOUNT
  EXECUTE msdb.dbo.sp_send_dbmail
	@recipients				= N'DBANotifications@US.BBAAviation.com',
	@subject				= N'Watchdog Alert: Idle Distribution Job(s)',
	@importance				= N'High',
	@query					= @tsql_command,
	@query_result_no_padding= 1,
	@query_result_separator	= N'	';
GO

USE DistributionDB;
SELECT s.name AS subscriber, msa.publication, msh.time
  FROM dbo.MSdistribution_history msh
	INNER JOIN dbo.MSdistribution_agents msa ON (msh.agent_id = msa.id)
	INNER JOIN master.sys.servers s ON (msa.subscriber_id = s.server_id)
  WHERE msh.runstatus = 3 AND msh.comments LIKE N'Delivered snapshot from the % sub-folder in % milliseconds';
GO